{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                日期      费用    展现量    点击量  订单金额    加购数  下单新客数  访问页面数    进店数  商品关注数\n",
      "日期        1.000000  0.674204  0.735885  0.858541  0.790643  0.704696    0.820946    0.849946  0.375041    0.192436\n",
      "费用        0.674204  1.000000  0.856013  0.858597  0.625787  0.601735    0.642448    0.763320  0.650899    0.155748\n",
      "展现量      0.735885  0.856013  1.000000  0.938554  0.728037  0.751283    0.756107    0.847017  0.697591    0.209990\n",
      "点击量      0.858541  0.858597  0.938554  1.000000  0.854883  0.815858    0.863694    0.910142  0.585917    0.205446\n",
      "订单金额    0.790643  0.625787  0.728037  0.854883  1.000000  0.813694    0.947238    0.803193  0.465630    0.279830\n",
      "加购数      0.704696  0.601735  0.751283  0.815858  0.813694  1.000000    0.809087    0.776379  0.471594    0.312882\n",
      "下单新客数  0.820946  0.642448  0.756107  0.863694  0.947238  0.809087    1.000000    0.842903  0.485570    0.361718\n",
      "访问页面数  0.849946  0.763320  0.847017  0.910142  0.803193  0.776379    0.842903    1.000000  0.541397    0.327500\n",
      "进店数      0.375041  0.650899  0.697591  0.585917  0.465630  0.471594    0.485570    0.541397  1.000000    0.393864\n",
      "商品关注数  0.192436  0.155748  0.209990  0.205446  0.279830  0.312882    0.361718    0.327500  0.393864    1.000000\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.unicode.east_asian_width',True)\n",
    "pd.set_option('expand_frame_repr',False)\n",
    "df = pd.read_excel('营销和产量销售表.xlsx')\n",
    "print(df.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按渠道的总额降序排名，以及按排名升序排序:\n",
      "             价格（元）  排名\n",
      "渠道                        \n",
      "新闻平台    5634481.82   1.0\n",
      "QQ          3094111.80   2.0\n",
      "浏览器      2981624.82   3.0\n",
      "微博        2831106.16   4.0\n",
      "短视频平台  2621087.73   5.0\n",
      "微信        2062663.38   6.0\n",
      "渠道和是否点击的交叉表:\n",
      " 是否点击          否        是      比例\n",
      "渠道                                    \n",
      "QQ          0.177020  0.003865  0.180885\n",
      "微信        0.089376  0.007730  0.097105\n",
      "微博        0.089134  0.058094  0.147228\n",
      "新闻平台    0.249889  0.013567  0.263457\n",
      "浏览器      0.079915  0.081364  0.161279\n",
      "短视频平台  0.120979  0.029067  0.150046\n",
      "比例        0.806313  0.193687  1.000000\n",
      "点击次数:\n",
      " 性别       女       男\n",
      "性别                  \n",
      "女    1.00000  0.78678\n",
      "男    0.78678  1.00000\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.unicode.east_asian_width',True)\n",
    "df = pd.read_excel('互联网广告智能投放数据.xlsx')\n",
    "df['投放时间']=pd.to_datetime(df['投放时间']).dt.hour\n",
    "df1 = df.groupby('渠道').agg({'价格（元）':'sum'})\n",
    "df1['排名']=df1.rank(method='first',ascending=False)\n",
    "df1.sort_values(by='排名',ascending=True,inplace=True)\n",
    "print('按渠道的总额降序排名，以及按排名升序排序:\\n',df1)\n",
    "df2 = pd.crosstab(index=df['渠道'],columns=df['是否点击'],\n",
    "                 margins_name='比例',margins=True,normalize=True)\n",
    "print('渠道和是否点击的交叉表:\\n',df2)\n",
    "df=pd.get_dummies(df,prefix='',prefix_sep='',columns=['是否点击'])\n",
    "df3=pd.pivot_table(df,values='是',index='渠道',columns='性别',aggfunc='sum')\n",
    "print('点击次数:\\n',df3.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
